<?php
	define('CONFIG_FILE',true);
	include '../config.php';
	define('DB_FILE',true);

	require_once '../Tool/DB/SqlDBManager.class.php';
	require_once '../Tool/Common/PrepareInput.php';
	
	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}
	$sqlDBManager = new SqlDBManager();
		
	if(!empty($_SESSION["userType"]))
	{
		$userType = $_SESSION["userType"];
	}
	
	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'NRIC';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'desc';
	$NRIC = $CustomerName=$CentreId='';

	if(!empty($_POST['NRIC'])){
		$NRIC =$_POST['NRIC'];
	}
	if(!empty($_POST['CustomerName'])){
		$CustomerName =$_POST['CustomerName'];
	}
	if(!empty($_POST['CentreLocation'])){
		$CentreId =$_POST['CentreLocation'];
	}

	if(!empty($_POST['SignInTimeStamp'])){
		$SignInTimeStamp=date('Y-m-d',strtotime($_POST['SignInTimeStamp']));
	}else{
		$SignInTimeStamp = '';
	}
	if($CentreId==0){
		$CentreId="";
	}      
	
	$offset = ($page-1)*$rows;
	$result = array();
	
	if($userType=="System Admin"){
		//Filter the $searchid
		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);
		
		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);
		
		$sql = "SELECT count(*)
			FROM attendancerecord a, client, centre
			WHERE a.NRIC = client.NRIC AND a.CentreId = Centre.CentreId
			AND a.NRIC LIKE ?
			AND client.CustomerName like ?
			AND a.SignInTimeStamp LIKE ?
			AND centre.CentreId LIKE ?";
				
		$parameters = array("%".$NRIC."%","%".$CustomerName."%","%".$SignInTimeStamp."%","%".$CentreId."%");
		
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
				
		$sql ="SELECT a.AttendanceRecordID, a.NRIC, a.SignInTimeStamp, a.SignOutTimeStamp, a.Status, client.CustomerName, centre.CentreName 
			FROM attendancerecord a, client, centre 
			WHERE a.NRIC = client.NRIC AND a.CentreId = Centre.CentreId 
			AND a.NRIC LIKE ? 
			AND client.CustomerName like ? 
			AND a.SignInTimeStamp LIKE ? 
			AND centre.CentreId LIKE ?
			order by $sort $order limit $offset,$rows";		
		
		$parameters = array("%".$NRIC."%","%".$CustomerName."%","%".$SignInTimeStamp."%","%".$CentreId."%");
		
		$res=$sqlDBManager->queryRows($sql,$parameters);	
		
		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++)
			{
				array_push($items, $res[$i]);		
			}	
		}
		//close connection
		$sqlDBManager->close_connect();
		
		$result["rows"] = $items;
		echo json_encode($result);
	}else{
		if(!empty($_SESSION["centreName"])){   
			$centreName = $_SESSION["centreName"];
		  }  
		//Filter the $searchid
		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);
		
		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);
		
		$sql="SELECT count(*)
			FROM attendancerecord a, client, centre
			WHERE a.NRIC = client.NRIC AND a.CentreId = Centre.CentreId
			AND a.NRIC LIKE ?
			AND client.CustomerName like ?
			AND a.SignInTimeStamp LIKE ?
			AND centre.CentreName =?";	
					
		$parameters = array("%".$NRIC."%","%".$CustomerName."%","%".$SignInTimeStamp."%",$centreName);
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
		//Filter the $searchid
		$searchid=addslashes($NRIC);
		$searchid=str_replace("%", "\%", $NRIC);
		$searchid=str_replace("_", "\_", $NRIC);
		
		$searchid=addslashes($CustomerName);
		$searchid=str_replace("%", "\%", $CustomerName);
		$searchid=str_replace("_", "\_", $CustomerName);
		
		$sql ="SELECT a.AttendanceRecordID, a.NRIC, a.SignInTimeStamp, a.SignOutTimeStamp, a.Status, client.CustomerName, centre.CentreName
			FROM attendancerecord a, client, centre
			WHERE a.NRIC = client.NRIC AND a.CentreId = Centre.CentreId
			AND a.NRIC LIKE ?
			AND client.CustomerName like ?
			AND a.SignInTimeStamp LIKE  ?
			AND centre.CentreName=?
			order by $sort $order
			limit $offset,$rows";		
		
		$parameters = array("%".$NRIC."%","%".$CustomerName."%","%".$SignInTimeStamp."%",$centreName);
		$res=$sqlDBManager->queryRows($sql,$parameters);	
		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++)
			{
				array_push($items, $res[$i]);		
			}	
		}
		
		//close connection
		$sqlDBManager->close_connect();
		
		$result["rows"] = $items;
		echo json_encode($result);
	}	
?>